package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import com.xwdeshj.utils.StringUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.web.bind.annotation.ResponseBody;


/**
 * Created by BKWJA on 2019/1/17.
 * 定制游—SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_XwCustom {

    /**
     * 挑选农家
     * @param min_pricebudget    最小人均预算
     * @param max_pricebudget    最大人均预算
     * @param playType_encryptId 农家游玩类型加密id
     * @param palydesc           农家游玩类型
     * @param destination        目的地
     * @param otherdemand        其他需求
     * @return
     * @throws Exception
     */
    public JSONObject getfarm_encryptId(double min_pricebudget, double max_pricebudget, String playType_encryptId,
                                    String palydesc, String destination, String otherdemand, JdbcTemplate jdbcTemplate) throws Exception {

        JSONObject farmIdObject = new JSONObject();
        try {

            String sql = "SELECT\n" +
                    "  a.farm_encryptId,\n" +
                    "  b.business_CompanyName AS CompanyName\n" +
                    "  from xw_farm_basicinfo  a\n" +
                    "LEFT JOIN xw_business_info b ON a.farm_encryptId = b.farm_encryptId \n";

            StringBuffer sb = new StringBuffer();
            sb.append(" 1=1 ");

            //人均预算
            if (!"".equals(min_pricebudget) || !"".equals(max_pricebudget)) {
                sb.append(" and (a.farm_consume BETWEEN " + min_pricebudget + " and " + max_pricebudget + ") \n");
            }

            //目的地匹配
            if (!"".equals(destination)) {
                //有多个目的地
                String[] tids = (StringUtil.ToDBC(destination)).split(",");
                for (int i = 0; i < tids.length; i++) {
                    if (tids.length == 1) {
                        //只选择了一个
                        sb.append(" and (a.farm_address like '%" + tids[i] + "%') \n");
                        break;
                    } else if (i == 0) { //有多个
                        sb.append(" and ");
                        sb.append(" (a.farm_address like '%" + tids[i] + "%' ");
                    } else if (tids.length - 1 == i) {
                        sb.append(" or a.farm_address like '%" + tids[i] + "%') \n");
                    } else {
                        sb.append(" or a.farm_address like '%" + tids[i] + "%' ");
                    }
                }
            }

            //游玩类型
            if (!"".equals(playType_encryptId) || !"".equals(palydesc)) {
                //有多个游玩类型
                String playtype = "";
                String[] tids = playType_encryptId.split(",");
                for (int i = 0; i < tids.length; i++) {
//                    if (i == 0) {
//                        playtype = ("'" + tids[i] + "'");
//                    } else {
//                        playtype += (",'" + tids[i] + "'");
//                    }
                    if (tids.length == 1) {
                        //只选择了一个
                        sb.append(" and (a.farm_playType_encryptId like '%" + tids[i] + "%') ");
                        break;
                    } else if (i == 0) { //有多个
                        sb.append(" and ");
                        sb.append(" (a.farm_playType_encryptId like '%" + tids[i] + "%' ");
                    } else if (tids.length - 1 == i) {
                        sb.append(" or a.farm_playType_encryptId like '%" + tids[i] + "%') \n");
                    } else {
                        sb.append(" or a.farm_playType_encryptId like '%" + tids[i] + "%' ");
                    }
                }
//                sb.append(" and a.farm_playType_encryptId in (" + playtype + ") ");
            }

            //其他需求
            if (!"".equals(otherdemand)) {
                String conn = "and";
                if (sb.length() > 2) {
                    conn = "or";
                }
                //有多个其他需求
                String[] tids = (StringUtil.ToDBC(otherdemand)).split(",");
                for (int i = 0; i < tids.length; i++) {
                    if (i == 0) {
                        sb.append(" " + conn + " a.farm_Intr_info like '%" + tids[i] + "%' \n");
                    } else {
                        sb.append(" or a.farm_Intr_info like '%" + tids[i] + "%' \n");
                    }
                }
            }

            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("shop_encryptId", rs.getString("farm_encryptId"));
                jsonObject.put("CompanyName", rs.getString("CompanyName"));
                jsonArray.add(jsonObject);
            }
            farmIdObject.put("shoplist", jsonArray);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return farmIdObject;
    }



    /**
     * 挑选景区
     * @param ticket             是否门票 0:否，1：是
     * @param min_pricebudget    门票价格 1：是
     * @param max_pricebudget    是否门票 1：是
     * @param playType_encryptId 游玩类型
     * @param destination        目的地
     * @param otherdemand        其他需求
     * @return
     */
    public JSONObject getsceinc_encryptId(double min_pricebudget, double max_pricebudget, String ticket, String playType_encryptId, String destination,
                                      String otherdemand, JdbcTemplate jdbcTemplate) throws Exception {

        JSONObject secIdObject = new JSONObject();
        try {

            String sql = "SELECT\n" +
                        "a.scenic_encryptId,\n" +
                        "b.business_CompanyName AS CompanyName\n" +
                    "FROM xw_scenic_basicinfo a\n" +
                    "LEFT JOIN xw_business_info b ON a.scenic_encryptId = b.sec_encryptId \n";

            StringBuffer sb = new StringBuffer();
            sb.append(" 1=1 ");

            //人均预算 (门票筛选)
            if (!"".equals(min_pricebudget) || !"".equals(max_pricebudget)) {
                sb.append(" and (a.scenic_Ticket BETWEEN " + min_pricebudget + " and " + max_pricebudget + ") \n");
            }

            //目的地匹配
            if (!"".equals(destination)) {
                //有多个目的地
                String[] tids = (StringUtil.ToDBC(destination)).split(",");
                for (int i = 0; i < tids.length; i++) {
                    if (tids.length == 1) {
                        //只选择了一个
                        sb.append(" and (a.scenic_address like '%" + tids[i] + "%') ");
                        break;
                    } else if (i == 0) { //有多个
                        sb.append(" and ");
                        sb.append(" (a.scenic_address like '%" + tids[i] + "%' ");
                    } else if (tids.length - 1 == i) {
                        sb.append(" or a.scenic_address like '%" + tids[i] + "%') \n");
                    } else {
                        sb.append(" or a.scenic_address like '%" + tids[i] + "%' ");
                    }
                }
            }

            //游玩类型
            if (!"".equals(playType_encryptId)) {
                //有多个游玩类型
                String playtype = "";
                String[] tids = playType_encryptId.split(",");
                for (int i = 0; i < tids.length; i++) {
//                    if (i == 0) {
//                        playtype = ("'" + tids[i] + "'");
//                    } else {
//                        playtype += (",'" + tids[i] + "'");
//                    }
                    if (tids.length == 1) {
                        //1个
                        sb.append(" and ( a.scenic_playType_encryptId like '%" + tids[i] + "%') \n");
                        break;
                    } else if (i == 0) { //多个
                        sb.append(" and ");
                        sb.append(" ( a.scenic_playType_encryptId like '%" + tids[i] + "%' ");
                    } else if (tids.length - 1 == i) {
                        sb.append(" or a.scenic_playType_encryptId like '%" + tids[i] + "%') \n");
                    } else {
                        sb.append(" or a.scenic_playType_encryptId like '%" + tids[i] + "%' ");
                    }
                }
            }

            //其他需求
            if (!"".equals(otherdemand)) {
                String conn = "and";
                if (sb.length() > 2) {
                    conn = "or";
                }
                //有多个其他需求
                String[] tids = (StringUtil.ToDBC(otherdemand)).split(",");
                for (int i = 0; i < tids.length; i++) {
                    if (i == 0) {
                        sb.append(" " + conn + " a.scenic_Intr_info like '%" + tids[i] + "%' \n ");
                    } else {
                        sb.append(" or a.scenic_Intr_info like '%" + tids[i] + "%' \n");
                    }
                }
            }

            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("shop_encryptId", rs.getString("scenic_encryptId"));
                jsonObject.put("CompanyName", rs.getString("CompanyName"));
                jsonArray.add(jsonObject);
            }
            secIdObject.put("shoplist", jsonArray);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return secIdObject;
    }


    //获取该用户对应的一条定制信息
    public JSONObject getcustominfo(String user_Encryptid, String cust_Encryptid, JdbcTemplate jdbcTemplate) {
        JSONObject result = new JSONObject();
        try {
            String sql = "SELECT\n" +
                    "  b.user_id AS user_id,\n" +
                    "  b.user_encryptId,\n" +
                    "  b.user_name AS username,\n" +
                    "  b.user_realName AS realName,\n" +
                    "  a.cust_encryptId,\n" +
                    "  a.cust_type,\n" +
                    "  a.cust_user_tel as cust_tel,\n" +
                    "  a.cust_user_mail as cust_mail,\n" +
                    "  a.cust_category AS category ,\n" +
                    "  a.cust_liveType AS roomtype,\n" +
                    "  a.cust_days,\n" +
                    "  a.cust_departure_time AS departuretime,\n" +
                    "  a.cust_Wizard,\n" +
                    "  a.cust_diningl,\n" +
                    "  a.cust_departure AS chufaArea,\n" +
                    "  a.cust_destination AS destination,\n" +
                    "  a.cust_Ticket,\n" +
                    "  a.cust_Per_budget AS pricebudget,\n" +
                    "  a.cust_paly,\n" +
                    "  a.cust_playTotalNum AS totalnum,\n" +
                    "  a.cust_palyChildNum as chilanum,\n" +
                    "  a.cust_palyOldNum AS oldnum,\n" +
                    "  a.cust_custom_time as createtime,\n" +
                    "  a.dataSources as dataSources,\n" +
                    "  a.cust_shop_CompanyName as CompanyName,\n" +
                    "  a.cust_other as other,\n" +
                    "  c.user_encryptId as applicat_encryptid,\n" +
                    "  c.user_name AS  applicatName\n" +
                    "FROM xw_custom a\n" +
                    "LEFT JOIN xw_userinfo b ON a.user_encryptId = b.user_encryptId\n" +
                    "  LEFT JOIN xw_userinfo c ON a.applicant_encryptId = c.user_encryptId ";

            StringBuffer sb = new StringBuffer();
            sb.append(" 1=1 ");
            //用户名加密id
            if (!user_Encryptid.equals("")) {
                sb.append(" and a.user_encryptId = '" + user_Encryptid + "' ");
            }
            //定制游加密id
            if (!cust_Encryptid.equals("")) {
                sb.append(" and a.cust_Encryptid = '" + cust_Encryptid + "' ");
            }

            sql = sql + " where " + sb.toString() + " ORDER BY a.cust_custom_time DESC  ";
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("user_id", rs.getString("user_id"));
                object.put("user_encryptId", rs.getString("user_encryptId"));
                object.put("username", rs.getString("username"));
                object.put("realName", rs.getString("realName") == null ? "" : rs.getString("realName"));
                object.put("cust_encryptId", rs.getString("cust_encryptId"));
                object.put("cust_type", rs.getString("cust_type") == null ? "" : rs.getString("cust_type"));
                object.put("cust_tel", rs.getString("cust_tel") == null ? "" : rs.getString("cust_tel"));
                object.put("cust_mail", rs.getString("cust_mail") == null ? "" : rs.getString("cust_mail"));
                object.put("category", rs.getString("category") == null ? "" : rs.getString("category"));
                object.put("roomtype", rs.getString("roomtype") == null ? "" : rs.getString("roomtype"));
                object.put("cust_days", rs.getString("cust_days") == null ? "" : rs.getString("cust_days"));
                object.put("departuretime", DateUtil.DateToString(rs.getDate("departuretime")));
                object.put("cust_Wizard", rs.getString("cust_Wizard") == null ? "" : rs.getString("cust_Wizard"));
                object.put("cust_diningl", rs.getString("cust_diningl") == null ? "" : rs.getString("cust_diningl"));
                object.put("chufaArea", rs.getString("chufaArea") == null ? "" : rs.getString("chufaArea"));
                object.put("destination", rs.getString("destination") == null ? "" : rs.getString("destination"));
                object.put("cust_Ticket", rs.getString("cust_Ticket") == null ? "" : rs.getString("cust_Ticket"));
                object.put("pricebudget", rs.getString("pricebudget") == null ? "" : rs.getString("pricebudget"));
                object.put("cust_paly", rs.getString("cust_paly") == null ? "" : rs.getString("cust_paly"));
                object.put("totalnum", rs.getString("totalnum") == null ? "" : rs.getString("totalnum"));
                object.put("chilanum", rs.getString("chilanum") == null ? "" : rs.getString("chilanum"));
                object.put("oldnum", rs.getString("oldnum") == null ? "" : rs.getString("oldnum"));
                object.put("dataSources", rs.getString("dataSources") == null ? "" : rs.getString("dataSources"));
                object.put("CompanyName", rs.getString("CompanyName") == null ? "未成功匹配农家/景区" : rs.getString("CompanyName"));
                object.put("other", rs.getString("other") == null ? "" : rs.getString("other"));
                object.put("createtime",DateUtil.DateToString(rs.getDate("createtime")));
                object.put("applicat_encryptid", rs.getString("applicat_encryptid") == null ? "" : rs.getString("applicat_encryptid"));
                object.put("applicatName", rs.getString("applicatName") == null ? "" : rs.getString("applicatName"));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);
            result.put("status", 0);
            result.put("msg", "ok");
        } catch (Exception e) {
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", "接口出现错误!");
        }
        return result;
    }


    //获取定制游列表
    public JSONObject getcustolist(Integer pageindex, Integer pagesize, String starttime, String endtime,
                                   String sortfild, String sortway, String type, String category,
                                   String datasource, String applicant,String searchval,String searchkey,
                                   String user_encryptId, JdbcTemplate jdbcTemplate) {
        JSONObject result = new JSONObject();
        String sql = "SELECT\n" +
                "  b.user_encryptId,\n" +
                "  b.user_name,\n" +
                "  b.user_realName,\n" +
                "  b.user_id,\n" +
                "  b.user_tel,\n" +
                "  a.cust_user_tel       AS tel,\n" +
                "  a.cust_id           AS cust_id,\n" +
                "  a.cust_encryptId      AS cust_encryptId,\n" +
                "  a.cust_type           AS type,\n" +
                "  a.cust_category       AS category,\n" +
                "  a.dataSources         AS dataSources,\n" +
                "  a.cust_shop_CompanyName      AS CompanyName,\n" +
                "  a.cust_custom_time    AS createtime,\n" +
                "  a.cust_shop_encryptId AS shop_encryptId,\n" +
                "  c.user_encryptId      AS applicant_encrptid,\n" +
                "  c.user_realName       AS applicatName\n" +
                "FROM xw_custom a\n" +
                "  LEFT JOIN xw_userinfo b ON a.user_encryptId = b.user_encryptId\n" +
                "  LEFT JOIN xw_userinfo c ON a.applicant_encryptId = c.user_encryptId";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //时间
        if (!starttime.equals("")) {
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and a.cust_custom_time >= '" + starttime + "' and a.cust_custom_time <= '" + endtime + "' ");
        }
        //申请人
        if (!applicant.equals("")) {
            sb.append(" and a.applicant_encryptId in('" + applicant + "') ");
        }
        //定制人
        if (!user_encryptId.equals("")) {
            sb.append(" and a.user_encryptId in('" + user_encryptId + "') ");
        }
        //数据来源
        if (!datasource.equals("")) {
            sb.append(" and a.dataSources in('" + datasource + "') ");
        }
        //挑选类型
        if (!category.equals("")) {
            sb.append(" and a.cust_category in('" + category + "') ");
        }
        //定制类型
        if (!type.equals("")) {
            sb.append(" and a.cust_type in('" + type + "') ");
        }
        //搜索内容查询
        if (!searchval.equals("")){
            if(searchkey.equals("user_name")){
                searchkey = "b.user_name";
            }else {
                searchkey = "a.cust_user_tel";
            }
            sb.append(" and ( "+searchkey+" like '%" +searchval + "%' )");
        }
        //排序
        if (!sortfild.equals("")) {
            sb.append(" \n order by a." + sortfild + " " + sortway + " ");
        }
        try {
            sql = sql + " \n where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = "SELECT\n" +
                    "  count(*)\n" +
                    "FROM xw_custom a\n" +
                    "  LEFT JOIN xw_userinfo b ON a.user_encryptId = b.user_encryptId\n" +
                    "  LEFT JOIN xw_userinfo c ON a.applicant_encryptId = c.user_encryptId  \n" +
                    " where " + sb.toString();

            //申请人下拉
            String sql3 = "SELECT\n" +
                    "  c.user_encryptId      AS applicant_encrptid,\n" +
                    "  c.user_realName       AS applicatName\n" +
                    "FROM xw_custom a\n" +
                    "LEFT JOIN xw_userinfo c ON a.applicant_encryptId = c.user_encryptId  \n" +
                    " WHERE "+sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            SqlRowSet rs3 = jdbcTemplate.queryForRowSet(sql3);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("applicant_encrptid", (rs.getString("applicant_encrptid") == null ? "" : rs.getString("applicant_encrptid")));
                object.put("applicatName", (rs.getString("applicatName") == null ? "" : rs.getString("applicatName")));
                object.put("user_encryptId", rs.getString("user_encryptId"));
                object.put("user_name", rs.getString("user_name"));
                object.put("user_realName", (rs.getString("user_realName") == null ? "" : rs.getString("user_realName")));
                object.put("user_id", (rs.getString("user_id") == null ? "" : rs.getString("user_id")));
                object.put("user_tel", (rs.getString("user_tel") == null ? "" : rs.getString("user_tel")));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("cust_id", rs.getString("cust_id"));
                object.put("cust_encryptId", (rs.getString("cust_encryptId") == null ? "" : rs.getString("cust_encryptId")));
                object.put("type", (rs.getString("type") == null ? "" : rs.getString("type")));
                object.put("category", (rs.getString("category") == null ? "" : rs.getString("category")));
                object.put("dataSources", (rs.getString("dataSources") == null ? "" : rs.getString("dataSources")));
                object.put("CompanyName", (rs.getString("CompanyName") == null ? "未成功匹配农家/景区" : rs.getString("CompanyName")));
                object.put("shop_encryptId", (rs.getString("shop_encryptId") == null ? "" : rs.getString("shop_encryptId")));
                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);

            JSONArray applicatArray = new JSONArray();
            while (rs3.next()){
                JSONObject object = new JSONObject();
                object.put("applicant_encrptid",rs3.getString("applicant_encrptid"));
                object.put("applicatName",rs3.getString("applicatName"));
                applicatArray.add(object);
            }
            result.put("applylist",applicatArray);

        } catch (Exception e) {
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }


    /**
     * 农家乐——获取某一次的匹配店铺详细信息
     */
    public JSONObject getcustomfarmAnduserlist(Integer pageindex,Integer pagesize,String sortfild,String sortway,
                                               String user_encryptId,String cust_encryptId,String shoptype,
                                               String business_type,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n " +
                "  c.business_type        AS businessTypeid,\n" +
                "  b.farm_type            AS shopTypeName,\n" +
                "  b.farmType_encryptId   AS shopType_encryptId,\n" +
                "  c.business_CompanyName AS CompanyName,\n" +
                "  c.business_Legal       AS Legal,\n" +
                "  c.business_tel         AS tel,\n" +
                "  c.business_name         AS business_name,\n" +
                "  b.farm_city            AS city,\n" +
                "  b.farm_address         AS address,\n" +
                "  b.farm_Intr_info       AS Intr_info,\n" +
                "  b.farm_consume         AS consume,\n" +
                "  b.farm_palytype_desc   AS playtype,\n" +
                "  e.cust_custom_time     AS createtime,\n" +
                "  \n" +
                "  a.cust_encryptId       AS cust_encryptId,\n" +
                "  a.user_encryptId       AS user_encryptId,\n" +
                "  b.business_encryptId   AS business_encryptId,\n" +
                "  b.farm_encryptId       AS shop_encryptId\n" +
                "\n" +
                "FROM xw_custom_shop a\n" +
                "  LEFT JOIN xw_farm_basicinfo b ON a.shop_encryptId = b.farm_encryptId\n" +
                "  LEFT JOIN xw_business_info c ON b.farm_encryptId = c.farm_encryptId\n" +
                "  LEFT JOIN xw_custom e ON a.cust_encryptId = e.cust_encryptId\n" +
                "                           AND a.user_encryptId = e.user_encryptId \n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //用户
        if(!user_encryptId.equals("")){
            sb.append(" and a.user_encryptId = '"+user_encryptId+"' \n");
        }
        //定制
        if(!cust_encryptId.equals("")){
            sb.append(" and a.cust_encryptId = '"+cust_encryptId+"' \n");
        }
        //店铺类型
        if(!shoptype.equals("")){
            sb.append(" and b.farm_type in('"+shoptype+"') \n");
        }
        //商户类型
        if(!business_type.equals("")){
            sb.append(" and c.business_type in("+business_type+") \n");
        }
        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by e." + sortfild + " " + sortway + " \n");
        }

        try {

            sql = sql + " where "+sb.toString()+" limit " + (pageindex - 1) * pagesize + "," + pagesize;

            //店铺类型下拉
            String sql3 = "SELECT\n" +
                    "  b.farmType_encryptId       AS shopType_encryptId,\n" +
                    "  b.farm_type            AS shopTypeName\n" +
                    "FROM xw_custom_shop a\n" +
                    "LEFT JOIN xw_farm_basicinfo b ON a.shop_encryptId = b.farm_encryptId \n" +
                    "LEFT JOIN xw_business_info c ON b.farm_encryptId = c.farm_encryptId\n" +
                    "LEFT JOIN xw_custom e ON a.cust_encryptId = e.cust_encryptId \n"+
                    " WHERE " + sb.toString();

            String sql2 = "SELECT\n" +
                    "  count(*)\n" +
                    "FROM xw_custom_shop a\n" +
                    "  LEFT JOIN xw_farm_basicinfo b ON a.shop_encryptId = b.farm_encryptId\n" +
                    "  LEFT JOIN xw_business_info c ON b.farm_encryptId = c.farm_encryptId\n" +
                    "  LEFT JOIN xw_custom e ON a.cust_encryptId = e.cust_encryptId\n" +
                    "                           AND a.user_encryptId = e.user_encryptId \n"+
                    " WHERE " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            SqlRowSet rs3 = jdbcTemplate.queryForRowSet(sql3);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("businessTypeid", (rs.getString("businessTypeid") == null ? "" : rs.getString("businessTypeid")));
                object.put("shopTypeName", (rs.getString("shopTypeName") == null ? "" : rs.getString("shopTypeName")));
                object.put("shopType_encryptId", (rs.getString("shopType_encryptId") == null ? "" : rs.getString("shopType_encryptId")));
                object.put("CompanyName", (rs.getString("CompanyName") == null ? "" : rs.getString("CompanyName")));
                object.put("Legal", (rs.getString("Legal") == null ? "" : rs.getString("Legal")));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("business_name", (rs.getString("business_name") == null ? "" : rs.getString("business_name")));
                object.put("city", (rs.getString("city") == null ? "" : rs.getString("city")));
                object.put("address", (rs.getString("address") == null ? "" : rs.getString("address")));
                object.put("Intr_info", (rs.getString("Intr_info") == null ? "" : rs.getString("Intr_info")));
                object.put("consume", (rs.getString("consume") == null ? "" : rs.getString("consume")));
                object.put("playtype", (rs.getString("playtype") == null ? "" : rs.getString("playtype")));
                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));

                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("cust_encryptId", (rs.getString("cust_encryptId") == null ? "" : rs.getString("cust_encryptId")));
                object.put("user_encryptId", (rs.getString("user_encryptId") == null ? "" : rs.getString("user_encryptId")));
                object.put("shop_encryptId", (rs.getString("shop_encryptId") == null ? "" : rs.getString("shop_encryptId")));

                jsonArray.add(object);
            }
            result.put("list", jsonArray);

            //商户类型
            JSONArray shoparray = new JSONArray();
            while (rs3.next()){
                JSONObject object = new JSONObject();
                object.put("shopTypeName", rs3.getString("shopTypeName"));
                object.put("shopType_encryptId", rs3.getString("shopType_encryptId"));
                shoparray.add(object);
            }
            result.put("shopTypelist",shoparray);

        }catch (Exception e){
            e.printStackTrace();
            result.put("status",5000);
            result.put("msg",e);
        }

        return result;
    }



    /**
     * 景区——获取某一次的匹配店铺详细信息
     */
    public JSONObject getcustomScenicAnduserlist(Integer pageindex,Integer pagesize,String sortfild,String sortway,
                                               String user_encryptId,String cust_encryptId,String shoptype,
                                               String business_type,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  c.business_type        AS businessTypeid,\n" +
                "  b.scenicType_desc            AS shopTypeName,\n" +
                "  b.scenicType_encryptId   AS shopType_encryptId,\n" +
                "  c.business_CompanyName AS CompanyName,\n" +
                "  c.business_Legal       AS Legal,\n" +
                "  c.business_tel         AS tel,\n" +
                "  c.business_name         AS business_name,\n" +
                "  b.scenic_city            AS city,\n" +
                "  b.scenic_address         AS address,\n" +
                "  b.scenic_Intr_info       AS Intr_info,\n" +
                "  b.scenic_Ticket         AS consume,\n" +
                "  b.scenic_palytype_desc   AS playtype,\n" +
                "  e.cust_custom_time     AS createtime,\n" +
                "\n" +
                "  a.cust_encryptId       AS cust_encryptId,\n" +
                "  a.user_encryptId       AS user_encryptId,\n" +
                "  b.business_encryptId   AS business_encryptId,\n" +
                "  b.scenic_encryptId       AS shop_encryptId\n" +
                "\n" +
                "FROM xw_custom_shop a\n" +
                "  LEFT JOIN xw_scenic_basicinfo b ON a.shop_encryptId = b.scenic_encryptId\n" +
                "  LEFT JOIN xw_business_info c ON b.scenic_encryptId = c.sec_encryptId\n" +
                "  LEFT JOIN xw_custom e ON a.cust_encryptId = e.cust_encryptId AND a.user_encryptId = e.user_encryptId  ";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //用户
        if(!user_encryptId.equals("")){
            sb.append(" and a.user_encryptId = '"+user_encryptId+"' ");
        }
        //定制
        if(!cust_encryptId.equals("")){
            sb.append(" and a.cust_encryptId = '"+cust_encryptId+"' ");
        }
        //店铺类型
        if(!shoptype.equals("")){
            sb.append(" and b.scenicType_encryptId in('"+shoptype+"') ");
        }
        //商户类型
        if(!business_type.equals("")){
            sb.append(" and c.business_type in("+business_type+") ");
        }
        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by e." + sortfild + " " + sortway + " ");
        }

        try {

            sql = sql + " where "+sb.toString()+" limit " + (pageindex - 1) * pagesize + "," + pagesize;

            //店铺类型下拉
            String sql3 = "SELECT\n" +
                    "  b.scenicType_encryptId       AS shopType_encryptId,\n" +
                    "  b.scenicType_desc            AS shopTypeName\n" +
                    "FROM xw_custom_shop a\n" +
                    "  LEFT JOIN xw_scenic_basicinfo b ON b.scenic_encryptId = a.shop_encryptId\n" +
                    "  LEFT JOIN xw_business_info c ON b.scenic_encryptId = c.sec_encryptId\n" +
                    "  LEFT JOIN xw_custom e ON a.cust_encryptId = e.cust_encryptId \n"+
                    " WHERE " + sb.toString();

            String sql2 = "SELECT\n" +
                    " count(*)\n" +
                    "FROM xw_custom_shop a\n" +
                    "  LEFT JOIN xw_scenic_basicinfo b ON a.shop_encryptId = b.scenic_encryptId\n" +
                    "  LEFT JOIN xw_business_info c ON b.scenic_encryptId = c.sec_encryptId\n" +
                    "  LEFT JOIN xw_custom e ON a.cust_encryptId = e.cust_encryptId AND a.user_encryptId = e.user_encryptId \n"+
                    " WHERE " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            SqlRowSet rs3 = jdbcTemplate.queryForRowSet(sql3);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("businessTypeid", (rs.getString("businessTypeid") == null ? "" : rs.getString("businessTypeid")));
                object.put("shopTypeName", (rs.getString("shopTypeName") == null ? "" : rs.getString("shopTypeName")));
                object.put("shopType_encryptId", (rs.getString("shopType_encryptId") == null ? "" : rs.getString("shopType_encryptId")));
                object.put("CompanyName", (rs.getString("CompanyName") == null ? "" : rs.getString("CompanyName")));
                object.put("Legal", (rs.getString("Legal") == null ? "" : rs.getString("Legal")));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("business_name", (rs.getString("business_name") == null ? "" : rs.getString("business_name")));
                object.put("city", (rs.getString("city") == null ? "" : rs.getString("city")));
                object.put("address", (rs.getString("address") == null ? "" : rs.getString("address")));
                object.put("Intr_info", (rs.getString("Intr_info") == null ? "" : rs.getString("Intr_info")));
                object.put("consume", (rs.getString("consume") == null ? "" : rs.getString("consume")));
                object.put("playtype", (rs.getString("playtype") == null ? "" : rs.getString("playtype")));
                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));

                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("cust_encryptId", (rs.getString("cust_encryptId") == null ? "" : rs.getString("cust_encryptId")));
                object.put("user_encryptId", (rs.getString("user_encryptId") == null ? "" : rs.getString("user_encryptId")));
                object.put("shop_encryptId", (rs.getString("shop_encryptId") == null ? "" : rs.getString("shop_encryptId")));

                jsonArray.add(object);
            }
            result.put("list", jsonArray);

            //商户类型
            JSONArray shoparray = new JSONArray();
            while (rs3.next()){
                JSONObject object = new JSONObject();
                object.put("shopTypeName", rs3.getString("shopTypeName"));
                object.put("shopType_encryptId", rs3.getString("shopType_encryptId"));
                shoparray.add(object);
            }
            result.put("shopTypelist",shoparray);

        }catch (Exception e){
            e.printStackTrace();
            result.put("status",5000);
            result.put("msg",e);
        }

        return result;
    }


}
